
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/23/2014 12:17:17
-- Generated from EDMX file: \\vmware-host\shared folders\Documents\Visual Studio 2012\Projects\eProcurement\eProcurement\eProcurement.DAL\eProcurementModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [eProcurement];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_PurchaserProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Projects] DROP CONSTRAINT [FK_PurchaserProject];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectBid]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Bids] DROP CONSTRAINT [FK_ProjectBid];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectAnnouncement]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Announcements] DROP CONSTRAINT [FK_ProjectAnnouncement];
GO
IF OBJECT_ID(N'[dbo].[FK_SupplierBid]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Bids] DROP CONSTRAINT [FK_SupplierBid];
GO
IF OBJECT_ID(N'[dbo].[FK_Purchaser_inherits_AppUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AppUsers_Purchaser] DROP CONSTRAINT [FK_Purchaser_inherits_AppUser];
GO
IF OBJECT_ID(N'[dbo].[FK_Supplier_inherits_AppUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AppUsers_Supplier] DROP CONSTRAINT [FK_Supplier_inherits_AppUser];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[AppUsers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AppUsers];
GO
IF OBJECT_ID(N'[dbo].[Projects]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Projects];
GO
IF OBJECT_ID(N'[dbo].[Bids]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Bids];
GO
IF OBJECT_ID(N'[dbo].[Announcements]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Announcements];
GO
IF OBJECT_ID(N'[dbo].[ErrorLogs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ErrorLogs];
GO
IF OBJECT_ID(N'[dbo].[AppUsers_Purchaser]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AppUsers_Purchaser];
GO
IF OBJECT_ID(N'[dbo].[AppUsers_Supplier]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AppUsers_Supplier];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'AppUsers'
CREATE TABLE [dbo].[AppUsers] (
    [Id] uniqueidentifier  NOT NULL,
    [Username] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [JobTitle] nvarchar(max)  NULL,
    [Telephone] nvarchar(max)  NOT NULL,
    [Fax] nvarchar(max)  NULL,
    [CompanyName] nvarchar(max)  NOT NULL,
    [Website] nvarchar(max)  NULL,
    [Address1] nvarchar(max)  NOT NULL,
    [Address2] nvarchar(max)  NULL,
    [City] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [DateJoined] datetime  NOT NULL
);
GO

-- Creating table 'Projects'
CREATE TABLE [dbo].[Projects] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [PurchaserId] uniqueidentifier  NOT NULL,
    [ScopeDocument] varbinary(max)  NOT NULL,
    [AdditionalDocument] varbinary(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [CloseDate] datetime  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [ScopeFileName] nvarchar(max)  NOT NULL,
    [AdditionalDocsName] nvarchar(max)  NOT NULL,
    [DatePosted] datetime  NOT NULL,
    [IndustryCategory] nvarchar(max)  NOT NULL,
    [State] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Bids'
CREATE TABLE [dbo].[Bids] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ProjectId] int  NOT NULL,
    [SupplierId] uniqueidentifier  NOT NULL,
    [Documents] varbinary(max)  NOT NULL,
    [Filename] nvarchar(max)  NOT NULL,
    [DateUploaded] datetime  NOT NULL,
    [IsWinner] bit  NOT NULL
);
GO

-- Creating table 'Announcements'
CREATE TABLE [dbo].[Announcements] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ProjectId] int  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [DatePosted] datetime  NOT NULL
);
GO

-- Creating table 'ErrorLogs'
CREATE TABLE [dbo].[ErrorLogs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [DateLogged] datetime  NOT NULL
);
GO

-- Creating table 'AppUsers_Purchaser'
CREATE TABLE [dbo].[AppUsers_Purchaser] (
    [PurchaserId] uniqueidentifier  NOT NULL,
    [pFirstname] nvarchar(max)  NOT NULL,
    [pLastName] nvarchar(max)  NOT NULL,
    [CC] nvarchar(max)  NOT NULL,
    [ExpMonth] nvarchar(max)  NOT NULL,
    [ExpYear] nvarchar(max)  NOT NULL,
    [Code] nvarchar(max)  NOT NULL,
    [pAddress1] nvarchar(max)  NOT NULL,
    [pAddress2] nvarchar(max)  NOT NULL,
    [pCity] nvarchar(max)  NOT NULL,
    [Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'AppUsers_Supplier'
CREATE TABLE [dbo].[AppUsers_Supplier] (
    [SupplierId] uniqueidentifier  NOT NULL,
    [Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'AppUsers'
ALTER TABLE [dbo].[AppUsers]
ADD CONSTRAINT [PK_AppUsers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [PK_Projects]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Bids'
ALTER TABLE [dbo].[Bids]
ADD CONSTRAINT [PK_Bids]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Announcements'
ALTER TABLE [dbo].[Announcements]
ADD CONSTRAINT [PK_Announcements]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ErrorLogs'
ALTER TABLE [dbo].[ErrorLogs]
ADD CONSTRAINT [PK_ErrorLogs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AppUsers_Purchaser'
ALTER TABLE [dbo].[AppUsers_Purchaser]
ADD CONSTRAINT [PK_AppUsers_Purchaser]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AppUsers_Supplier'
ALTER TABLE [dbo].[AppUsers_Supplier]
ADD CONSTRAINT [PK_AppUsers_Supplier]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [PurchaserId] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [FK_PurchaserProject]
    FOREIGN KEY ([PurchaserId])
    REFERENCES [dbo].[AppUsers_Purchaser]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PurchaserProject'
CREATE INDEX [IX_FK_PurchaserProject]
ON [dbo].[Projects]
    ([PurchaserId]);
GO

-- Creating foreign key on [ProjectId] in table 'Bids'
ALTER TABLE [dbo].[Bids]
ADD CONSTRAINT [FK_ProjectBid]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[Projects]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectBid'
CREATE INDEX [IX_FK_ProjectBid]
ON [dbo].[Bids]
    ([ProjectId]);
GO

-- Creating foreign key on [ProjectId] in table 'Announcements'
ALTER TABLE [dbo].[Announcements]
ADD CONSTRAINT [FK_ProjectAnnouncement]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[Projects]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectAnnouncement'
CREATE INDEX [IX_FK_ProjectAnnouncement]
ON [dbo].[Announcements]
    ([ProjectId]);
GO

-- Creating foreign key on [SupplierId] in table 'Bids'
ALTER TABLE [dbo].[Bids]
ADD CONSTRAINT [FK_SupplierBid]
    FOREIGN KEY ([SupplierId])
    REFERENCES [dbo].[AppUsers_Supplier]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SupplierBid'
CREATE INDEX [IX_FK_SupplierBid]
ON [dbo].[Bids]
    ([SupplierId]);
GO

-- Creating foreign key on [Id] in table 'AppUsers_Purchaser'
ALTER TABLE [dbo].[AppUsers_Purchaser]
ADD CONSTRAINT [FK_Purchaser_inherits_AppUser]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[AppUsers]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'AppUsers_Supplier'
ALTER TABLE [dbo].[AppUsers_Supplier]
ADD CONSTRAINT [FK_Supplier_inherits_AppUser]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[AppUsers]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------